CREATE view [dbo].[vDonationsDetail] as
   select max(Activity.ID)                                       ID,
              max(Activity.ORIGINATING_TRANS_NUM) OriginalTransaction,
                  max(P.INVOICE_REFERENCE_NUM) as  InvoiceRefNum,
              max(Activity.SOURCE_SYSTEM)                  SourceSystem,
              max(Activity.TRANSACTION_DATE)                     TransactionDate,
              max(Activity.EFFECTIVE_DATE)                  DateReceived,
              (sum(C.AMOUNT) * -1)  as                          Amount,
              max(Activity.SOLICITOR_ID)                      SolicitorID,
              (case when max(Activity.ACTIVITY_TYPE) = 'GIFT' then
                            max(P.CHECK_NUMBER)
                            else '' end)                            CheckNumber,
                 max(Activity.SOURCE_CODE) Appeal,
                  max(Activity.CAMPAIGN_CODE) Campaign,
                  max(Activity.ORG_CODE) Fund,
                  (case when max(Activity.ACTIVITY_TYPE) = 'GIFT' then (case max(Cash_Accounts.ACCOUNT_TYPE)
                            when 1 then 'Credit Card'
                            when 2 then 'In Kind'
                            when 3 then 'Debit Card'
                           else 'Cash' end) else '' end) as PaymentType,
              CONVERT(int, SUBSTRING(CONVERT(char(6),max( P.FISCAL_PERIOD)), 5, 2)) AS FiscalMonth,   
              convert(int,substring(convert(char(6),max(P.FISCAL_PERIOD)),1,4)) as FiscalYear,
              (case when max(Activity.ACTIVITY_TYPE) = 'GIFT'
                        then 'Gift'
                        else 'Pledge' end) as                            GiftType,
              max(P.MATCH_GIFT_TRANS_NUM)              MatchingTransaction,
              max(P.IS_MATCH_GIFT)                               IsMatchingGift,
              max(P.MEM_TRIB_ID)                                   MemorialID,
              max(Activity.ACTION_CODES)                     ListAs,
              max(Activity.UF_4)                                       RequestNumber,
              max(P.MEM_TRIB_NAME_TEXT)                   MemorialNameText  
       from Trans P inner join Activity on  P.ACTIVITY_SEQN = Activity.SEQN
                            left outer join Cash_Accounts on P.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
                            inner join Invoice on  Invoice.REFERENCE_NUM = P.INVOICE_REFERENCE_NUM
                            inner join Trans C on Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
      Where        C.TRANSACTION_TYPE = 'DIST' and
                       P.JOURNAL_TYPE = 'IN' and P.TRANSACTION_TYPE = 'DIST'
                            and P.PRODUCT_CODE = C.PRODUCT_CODE
                            AND (Invoice.SOURCE_SYSTEM = 'FR' OR  
                            (Invoice.SOURCE_SYSTEM = 'DUES' AND  P.INVOICE_LINE_NUM = C.INVOICE_LINE_NUM))
                            and C.IS_FR_ITEM = 1  
   group by Activity.ID, Activity.ORIGINATING_TRANS_NUM, Invoice.REFERENCE_NUM, Activity.ORG_CODE, Activity.CAMPAIGN_CODE, Activity.SOURCE_CODE
    union
    select max(Activity.ID)                        ID,
           min(Invoice.ORIGINATING_TRANS_NUM)    as OriginalTransaction,
           max(Trans.INVOICE_REFERENCE_NUM)    InvoiceRefNum,
           max(Activity.SOURCE_SYSTEM)         SourceSystem,
           max(Activity.TRANSACTION_DATE)  TransactionDate,
           max(Activity.EFFECTIVE_DATE)         DateReceived,
           sum(Activity.AMOUNT) as        Amount,
           '' as                                                   SolicitorID,
           '' as                                                   CheckNumber,
                 max(Activity.SOURCE_CODE) Appeal,
                  max(Activity.CAMPAIGN_CODE) Campaign,
                  max(Activity.ORG_CODE) Fund,
                '' as PaymentType,
           CONVERT(int, SUBSTRING(CONVERT(char(6), max(Trans.FISCAL_PERIOD)), 5, 2)) AS FiscalMonth,     
           convert(int,substring(convert(char(6),max(Trans.FISCAL_PERIOD)),1,4)) as FiscalYear,
           'Gift' as                                           GiftType,
           0 as                                                MatchingTransaction,
           0 as                                                IsMatchingGift,
           '' as                                                 MemorialID,
           '' as                                                 ListAs,
           0 as                                                RequestNumber,
           '' as                                                 MemorialNameText     
       from Activity
       inner join Trans on Activity.ORIGINATING_TRANS_NUM = Trans.TRANS_NUMBER
       inner join Invoice on Invoice.REFERENCE_NUM = Trans.INVOICE_REFERENCE_NUM
       where
             Activity.ACTIVITY_TYPE = 'GIFT' and
             Activity.SOURCE_SYSTEM = 'MEETING' and
             Trans.TRANSACTION_TYPE = 'DIST' and
              (Trans.PRODUCT_CODE = Activity.PRODUCT_CODE OR Trans.PRODUCT_CODE LIKE Activity.PRODUCT_CODE +'/%')
    group by Activity.ID, Trans.INVOICE_REFERENCE_NUM, Activity.ORG_CODE, Activity.CAMPAIGN_CODE, Activity.SOURCE_CODE
    union
    select max(Activity.ID)                        ID,
           max(Activity.ORIGINATING_TRANS_NUM)  as OriginalTransaction,
           0 as                                                   InvoiceRefNum,
           max(Activity.SOURCE_SYSTEM)         SourceSystem,
           max(Activity.TRANSACTION_DATE)  TransactionDate,
           max(Activity.EFFECTIVE_DATE)         DateReceived,
           sum(Activity.AMOUNT) as        Amount,
           '' as                                                   SolicitorID,
           max(Trans.CHECK_NUMBER) as                                  CheckNumber,
           max(Activity.SOURCE_CODE) Appeal,
           max(Activity.CAMPAIGN_CODE) Campaign,
           max(Activity.ORG_CODE) Fund,
           (case max(Cash_Accounts.ACCOUNT_TYPE)
                            when 1 then 'Credit Card'
                            when 2 then 'In Kind'
                            when 3 then 'Debit Card'
                            else 'Cash' end) as PaymentType,
           CONVERT(int, SUBSTRING(CONVERT(char(6), max(Trans.FISCAL_PERIOD)), 5, 2)) AS FiscalMonth,     
           convert(int,substring(convert(char(6),max(Trans.FISCAL_PERIOD)),1,4)) as FiscalYear,
           'Gift' as                                           GiftType,
           0 as                                                MatchingTransaction,
           0 as                                                IsMatchingGift,
           '' as                                                 MemorialID,
           '' as                                                 ListAs,
           0 as                                                RequestNumber,
           '' as                                                 MemorialNameText     
       from dbo.Activity
            INNER JOIN Trans ON
                Activity.ORIGINATING_TRANS_NUM = Trans.TRANS_NUMBER AND
                Trans.ST_ID = Activity.ID AND
                Trans.PRODUCT_CODE = Activity.PRODUCT_CODE
            LEFT OUTER JOIN Cash_Accounts ON
                Trans.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
       where
             Activity.ACTIVITY_TYPE = 'GIFT' and
             Activity.SOURCE_SYSTEM IN ('DUES', 'SC') and
             Trans.TRANSACTION_TYPE = 'DIST'
    group by Activity.ID, Trans.TRANS_NUMBER, Activity.ORG_CODE, Activity.CAMPAIGN_CODE, Activity.SOURCE_CODE

GO
GRANT REFERENCES ON  [dbo].[vDonationsDetail] TO [IMIS]
GRANT SELECT ON  [dbo].[vDonationsDetail] TO [IMIS]
GRANT INSERT ON  [dbo].[vDonationsDetail] TO [IMIS]
GRANT DELETE ON  [dbo].[vDonationsDetail] TO [IMIS]
GRANT UPDATE ON  [dbo].[vDonationsDetail] TO [IMIS]
GO